package com.sds.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import javax.servlet.http.HttpSession;

import com.sds.fw.dao.AbDao;
import com.sds.shop.vo.BoardVO;
import com.sds.shop.vo.MemVO;
import com.sds.shop.vo.ReplyVO;
import com.sds.shop.vo.SessionVO;

public class ReplyDAO extends AbDao {
	private static ReplyDAO dao = new ReplyDAO();

	public static ReplyDAO getInstance() {
		return dao;
	}

	public Vector<ReplyVO> getReplysBoard(String board_no) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Vector<ReplyVO> list = new Vector<ReplyVO>();
		try {
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("select * from tb_reply where board_no=? and lower(del_flg) like 'n' order by to_number(seq) desc");

			ps = conn.prepareStatement(query.toString());
			ps.setString(1, board_no);

			rs = ps.executeQuery();

			while (rs.next()) {
				ReplyVO vo = new ReplyVO();
				vo.setBoard_no(rs.getString("board_no"));
				vo.setSeq(rs.getString("seq"));
				vo.setMem_id(rs.getString("mem_id"));
				vo.setMem_nm(rs.getString("mem_nm"));
				vo.setReply(rs.getString("reply"));
				vo.setDel_flg(rs.getString("del_flg"));
				vo.setReg_dtm(rs.getString("reg_dtm"));
				

				list.add(vo);
			}

			if (list.size() < 0) {
				return null;
			}

			close(conn, ps, rs);
		} catch (Exception ex) {
			System.out.println("getListBoard exception : " + ex);
			close(conn, ps, rs);
		}

		return list;
	}


	public boolean createReply(String board_no, String mem_id, String mem_nm, String reply) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Vector<BoardVO> list = new Vector<BoardVO>();
		try {
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("insert into tb_reply values(?, (select nvl(count(seq), '0')+1 from tb_reply where board_no=?), ");
			query.append("?, ?, ?, 'N', to_char(sysdate, 'YYYYMMDDHH24MISS'))");

			ps = conn.prepareStatement(query.toString());

			ps.setString(1, board_no);
			ps.setString(2, board_no);
			ps.setString(3, mem_id);
			ps.setString(4, mem_nm);
			ps.setString(5, reply);
			

			int index = ps.executeUpdate();

			if (index < 1) {
				return false;
			}
			close(conn, ps, rs);

		} catch (Exception ex) {
			System.out.println("createReply exception : " + ex);
			close(conn, ps);
			return false;
		}

		return true;
	}



	public boolean deleteReply(String board_no, String seq) {

		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("update tb_reply set del_flg='Y' where board_no=? and seq=?");

			ps = conn.prepareStatement(query.toString());

		
			ps.setString(1, board_no);
			ps.setString(2, seq);
			

			int index = ps.executeUpdate();

			if (index < 1) {
				return false;
			}
			close(conn, ps, rs);

		} catch (Exception ex) {
			System.out.println("deleteReply exception : " + ex);
			close(conn, ps);
			return false;
		}

			
		return true;
	}

}
